
package com.rasenhund.android.paintstock.dao;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.rasenhund.android.paintstock.schema.Paint;
import com.rasenhund.android.paintstock.schema.Stock;

/**
 * 在庫情報 Data Access Object
 */
public class StockDao extends BaseDao {

    public StockDao(Context context) {
        super(context);
    }

    /**
     * コンストラクタ(DB指定あり)
     * 
     * @param context
     * @param sDbName
     */
    public StockDao(Context context, String sDbName) {
        super(context, sDbName);
    }

    /**
     * 在庫情報の取得
     * 
     * @param sName - キーとなる塗料名
     * @return 対象データがない場合はnull
     */
    public Stock selectAt(String sName) {

        // TODO @@@ 塗料名称ではなくIDをキーにするように修正する

        Stock stock = null;

        // @formatter:off
        String sSql = "";
        sSql += "select Stock._id";
        sSql += "     , Stock.stock";
        sSql += "     , Stock.amount";
        sSql += "     , Paint._id";
//        sSql += "     , Paint.name";
        sSql += "  from Paint";
        sSql += "  left join Stock";
        sSql += "    on Paint._id = Stock.PaintId";
        sSql += " where Paint.name = '" + sName + "'";
        // @formatter:on

        SQLiteDatabase db = SQLiteDatabase.openDatabase(getDbPath(), null,
                SQLiteDatabase.NO_LOCALIZED_COLLATORS);

        try {
            Cursor cursor = db.rawQuery(sSql, null);
            cursor.moveToFirst();
            if (cursor.getCount() > 0) {

                stock = new Stock();

                stock.set_id(cursor.getInt(0));
                stock.setStock(cursor.getInt(1));
                stock.setAmount(cursor.getInt(2));
                stock.setPaintId(cursor.getInt(3));
                // stock.setPaintName(cursor.getString(4));
            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            db.close();
        }

        return stock;

    }

    public ArrayList<Stock> selectAll() {

        ArrayList<Stock> stockList = new ArrayList<Stock>();

        // @formatter:off
        String sSql = "";
        sSql += "select Stock._id";
        sSql += "     , Stock.paintId";
        sSql += "     , Stock.stock";
        sSql += "     , Stock.amount";
        sSql += "  from Stock";
        // @formatter:on

        SQLiteDatabase db = SQLiteDatabase.openDatabase(getDbPath(), null,
                SQLiteDatabase.NO_LOCALIZED_COLLATORS);

        try {

            Cursor cursor = db.rawQuery(sSql, null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                for (int i = 0; i < cursor.getCount(); i++) {

                    Stock stock = new Stock();

                    stock.set_id(cursor.getInt(0));
                    stock.setPaintId(cursor.getInt(1));
                    stock.setStock(cursor.getInt(2));
                    stock.setAmount(cursor.getInt(3));

                    stockList.add(stock);

                    cursor.moveToNext();
                }

            }

        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            db.close();
        }

        return stockList;

    }

    /**
     * 在庫あり一覧の取得
     * 
     * @return
     */
    public ArrayList<Stock> selectZaiko() {
        return selectZaiko(true);
    }

    /**
     * 在庫なしを含む全塗料一覧の取得
     * 
     * @return
     */
    public ArrayList<Stock> selectZaikoAriNashi() {
        return selectZaiko(false);
    }

    /**
     * 在庫一覧の取得
     * 
     * @param bIsStock Trueの場合、在庫ありのみ
     * @return
     */
    public ArrayList<Stock> selectZaiko(boolean bIsStock) {

        ArrayList<Stock> stockList = new ArrayList<Stock>();

        // @formatter:off
        String sSql = "";
        if (bIsStock) {
            // 在庫ありのみ
            sSql += "select Paint._id";
            sSql += "     , Paint.name";
            sSql += "     , Stock.stock";
            sSql += "     , Stock.amount";
            // sSql += "     , Paint.color";
            sSql += "  from Paint";
            sSql += " inner join Stock";
            sSql += "    on Paint._id = Stock.paintId";
            sSql += " where Stock.stock > 0";
            sSql += "    or Stock.amount > 0";
            sSql += " order by Paint.seriesId";
            sSql += "     , Paint.sort";
        } else {
            // 在庫なしも含む
            sSql += "select Paint._id";
            sSql += "     , Paint.name";
            sSql += "     , Stock.stock";
            sSql += "     , Stock.amount";
            // sSql += "     , Paint.color";
            sSql += "  from Paint";
            sSql += " left join Stock";
            sSql += "    on Paint._id = Stock.paintId";
            sSql += " order by Paint.seriesId";
            sSql += "     , Paint.sort";
        }
        // @formatter:on

        SQLiteDatabase db = SQLiteDatabase.openDatabase(getDbPath(), null,
                SQLiteDatabase.NO_LOCALIZED_COLLATORS);

        try {

            Cursor cursor = db.rawQuery(sSql, null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                for (int i = 0; i < cursor.getCount(); i++) {

                    Stock stock = new Stock();
                    Paint paint = new Paint();
                    
                    paint.set_id(cursor.getInt(0));
                    paint.setName(cursor.getString(1));
                    // paint.setColor(cursor.getString(4));

                    stock.setStock(cursor.getInt(2));
                    stock.setAmount(cursor.getInt(3));
                    stock.setPaint(paint);

                    stockList.add(stock);

                    cursor.moveToNext();
                }

            }

        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            db.close();
        }

        return stockList;

    }

    /**
     * 新規登録
     * 
     * @param stock
     * @return
     */
    public long insert(Stock stock) {

        long lRet = 0;

        ContentValues values = new ContentValues();
        values.put(Stock.Columns.PAINT_ID, stock.getPaintId());
        values.put(Stock.Columns.Stock, stock.getStock());
        values.put(Stock.Columns.Amount, stock.getAmount());

        SQLiteDatabase db = SQLiteDatabase.openDatabase(getDbPath(), null,
                SQLiteDatabase.NO_LOCALIZED_COLLATORS);

        try {
            lRet = db.insert(Stock.TABLE_NAME, null, values);
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            db.close();
        }

        return lRet;

    }

    /**
     * 新規登録(配列全件)
     * 
     * @param stocks
     * @return 処理件数 or -1(エラー時)
     */
    public long insertAll(ArrayList<Stock> stocks) {

        long lResult = 0;

        SQLiteDatabase db = SQLiteDatabase.openDatabase(getDbPath(), null,
                SQLiteDatabase.NO_LOCALIZED_COLLATORS);

        try {

            for (Stock stock : stocks) {

                ContentValues values = new ContentValues();
                values.put(Stock.Columns.PAINT_ID, stock.getPaintId());
                values.put(Stock.Columns.Stock, stock.getStock());
                values.put(Stock.Columns.Amount, stock.getAmount());

                if (db.insert(Stock.TABLE_NAME, null, values) == -1) {
                    // エラー時は処理中断
                    lResult = -1;
                    break;
                }

                lResult++;

            }

        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            db.close();
        }

        return lResult;

    }

    /**
     * 更新
     * 
     * @param stock
     * @return
     */
    public long update(Stock stock) {

        long lRet = 0;

        ContentValues values = new ContentValues();
        values.put(Stock.Columns.Stock, stock.getStock());
        values.put(Stock.Columns.Amount, stock.getAmount());

        String sWhere = Stock.Columns.ROW_ID + " = " + String.valueOf(stock.get_id());

        SQLiteDatabase db = SQLiteDatabase.openDatabase(getDbPath(), null,
                SQLiteDatabase.NO_LOCALIZED_COLLATORS);

        try {
            lRet = db.update(Stock.TABLE_NAME, values, sWhere, null);
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            db.close();
        }

        return lRet;

    }

}
